A database is an integrated collection of data records, files, and other database objects needed by an application. Database objects are stored in database tables that are accessible to a user. A database index is a data structure that improves the speed of data retrieval operations on a database table. A database management system (DBMS) is the software that controls the creation, maintenance, and the use of a database. The DBMS handles user queries for data.
Indexes are important for query performance because a good set of indexes can provide more efficient access to tables and speed up query processing. Indexes can improve performance by providing local filtering, making join processing more efficient, avoiding the need for sort processing, providing index-only access, enforcing referential integrity, and more. Consequently, indexing is a fundamental part of query tuning.
Clustered computing systems and associated components are a group of systems that work together and are often interconnected through a local area network (LAN). One example of such a cluster is a data center where servers and storage systems are stored in close proximity. Data centers tend to stress data security and largely focus on redundancy. This includes redundancy of processes, redundancy of data, redundant power supplies, and redundant data communications connections. In this manner, the probability of losing high value data, and access to that data, is minimized. Towards that end, data centers typically have a primary server, controlling access to the data center components, and at least one secondary server with a copy of the DBMS software.
An optimizer functionality (sometimes referred to as a query optimizer or database optimizer) is one function of the DBMS which determines the best way to execute a query received from a client. The optimizer examines possible paths through, for example, an index scan, and determines an optimal query plan, also referred to as an access plan, for the query received. The query plan is an ordered set of steps used to access or modify information in the DBMS.
Growing complexity of queries means that determining the correct indexes to create can be a challenging and difficult task. An index advisor is a functionality running as a part, or in conjunction with, the DBMS and provides detailed recommendations for indexes to create, based upon performance signatures of the database workload. When the resources are available, the recommended indexes are created and attached to the corresponding database tables.
Though client demand for availability of operation of a data center is constant, there are fluctuations in that demand throughout the day. For example, one or more clients may need to perform critical business operations at the close of the business day—and together, these operations demand a large amount of data center resources in a short amount of time. As these operations are performed, the index advisor may determine that new indexes should be created to enhance the efficiency of the operations. However, the subsequent index creation is a resource intensive task. When resources are already in high demand, building the more efficient indexes may decrease, or even negate, the sought after efficiency that comes with their creation.